package com.roc.service.impl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.BlackListBean;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.newPage.IPageDao;
import com.roc.pojo.Operators;
import com.roc.pojo.TReviseMerinfo;
import com.roc.service.ServiceException;
import com.roc.service.impl.AgncyServiceImpl;
import com.roc.service.reviseMerinfo.ReviseMerinfoService;
import com.roc.util.Constance;
import com.roc.util.constant.Role;
@Service
@Transactional(rollbackFor = { ServiceException.class })
public class ReviseMerinfoServiceImpl implements ReviseMerinfoService {
	@Autowired
	private IBaseDao baseDao;
	@Autowired
	private IPageDao pageDao;
	
	static Logger logger=Logger.getLogger(ReviseMerinfoServiceImpl.class);
	@Override
	public Map<String, Object> importMidList(File file, Operators op)throws ServiceException {
		   int count = 0;
		//上传文件错误原因及定位
		Map<String,Object> resultMap=new LinkedHashMap<String, Object>();
		//存放上传数据
		Map<Integer,List<Object>> uploadData=new LinkedHashMap<Integer, List<Object>>();
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel(file, (LinkedHashMap<Integer, List<Object>>) uploadData,(LinkedHashMap<String, Object>) resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		//导入数据库
				logger.info("开始进行批量导入操作");
				SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
				String date=sdf.format(new Date());
				Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
				//检测是否重复数据
				while(it.hasNext())
				{
					Entry<Integer, List<Object>> entry=it.next();
					List<Object> list=entry.getValue();
					//循环列					
					String mchtCd=Constance.Null2EmptyTrim(list.get(0));			
				/*	BlackListBean temp=findByBlackNum(mchtCd);
					if(temp!=null)
					{
						resultMap.put("result","导入失败,商户号或卡号【"+mchtCd+"】已存在!!!");
						logger.info("停止批量导入,已存在数据【"+mchtCd+"】");
						return resultMap;
					}*/
				}//开始导入数据
				int batchNo = 0;
				int  newBatch=0;
				String sql = "select  to_char(max(t.BATCH_NO)) from T_REVISE_MERINFO t  where  t.OP_ID='"+op.getId()+"' and SUBSTR(t.CREATEDATE, 1, 10)=to_char(sysdate,'yyyy-mm-dd')";
//				StringBuffer sb = new StringBuffer();
//				sb.append("select  to_char(max(t.BATCH_NO)) from T_REVISE_MERINFO t  where  1=1  ");
				 String ss;
					try {
						ss = (String) baseDao.createSQLQueryUniqueResult(sql);
						if("".equals(ss)||ss==null){
					    	batchNo=0;
					    }else {
					    	batchNo= Integer.valueOf(ss).intValue();
					    	
					    }
					   
					    if(batchNo==0){
					    	newBatch=1;
					    }else{
					    	newBatch=batchNo+1;
					    }
					} catch (DaoException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				    
				it=uploadData.entrySet().iterator();
				while(it.hasNext())
				{
					
					try {
				    	

					Entry<Integer, List<Object>> entry=it.next();
					List<Object> list=entry.getValue();
					//循环列					
					TReviseMerinfo revise=new TReviseMerinfo();
					revise.setMchtCd((String) list.get(0));
					revise.setOpId(String.valueOf(op.getId()));
					revise.setCreateDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
					revise.setBatchNo(newBatch);
				        baseDao.editSave(revise);
						count++;
						resultMap.put("result","成功导入商户号"+count+"个.批次号为【"+newBatch+"】.请牢记批次号！！ ");
					} catch (DaoException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}		
					
					
				}
				
				return resultMap;
			}

	/**
	 * 
	 * 根据条件查询整改商户信息
	 * 
	 */
	
	
	
	
	
	
	
	
	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,LinkedHashMap<Integer,List<Object>> map,LinkedHashMap<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<1){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>1){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号"};
			String titleName=null;
				row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
				titleName=row.getCell(0).getStringCellValue();
				if(!titleName.equals(title[0]))
				{
					reason_error="第1行第1列的列名不正确,正确的列名为【"+title[0]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常："+ e.getMessage());
			return false;
		}
		return true;
	}
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="\\d{1,15}";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号不规范,只能包含数字字符且长度不能超过15，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
	
	/**
     * 查询近3个月是否有交易
     * @param mchtCd
     * @return
     * @throws ServiceException
     */
	public int WhetherDeal(String mchtCd) throws ServiceException {
		String sql  =" select count(1) "
					+" from apstfr.tbl_tfr_his_trn_log ap " 
					+" where ap.trans_st = '1000000000'  and ap.resp_cd = '00' and ap.ma_trans_cd in ('1171', '1011') "
					+" and ap.mchnt_cd =? and ap.rec_crt_ts>sysdate-90  " ;
					 
		int count=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(mchtCd);
		try {
			count = pageDao.countByParams(sql, parmas);
			if(count>0){
				logger.info("查询该商户号【"+mchtCd+"】近三个月交易次数【"+count+"】次");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询商户号交易次数验证失败！");
		}
		
		return count;
  }
		
}
